<?php
/**
 * Created by PhpStorm.
 * User: Frings
 * Date: 2016/12/12
 * Time: 15:53
 */

namespace app\models;


use app\librarys\Tools;
use app\librarys\DbCacheUtility;

class Department
{
    /**
     * 编辑部门
     * @param $id
     * @param $name
     * @param $remark
     * @param $parent_id
     * @param $institution_code
     * @return bool
     */
    public static function saveDepartment($id, $name, $remark, $parent_id, $institution_code)
    {
        $sql = '';
        if ($id == '') {
            $sql = 'INSERT INTO t_department(id,institution_code,parent_id,code_tree,name,remark,is_delete,create_datetime)
                    VALUES (:id,:institution_code,:parent_id,:code_tree,:name,:remark,0,now())';
        } else {
            $tree = self::getDepartment($id)['code_tree'];
            $sql = 'UPDATE t_department SET name = :name,parent_id=:parent_id ,remark = :remark,code_tree=:code_tree WHERE id = :id';
        }

        if ($institution_code == '0') {
            $institution_code = '00000000';
        }
        $code_tree = self::getCodeTree($parent_id, $institution_code);


        $cmd = \Yii::$app->db->createCommand($sql);

        if ($id == '') {
            $cmd->bindValue(':id', Tools::db_uuid());
            $cmd->bindValue(':institution_code', $institution_code);
            $cmd->bindValue(':parent_id', $parent_id);

            $cmd->bindValue(':code_tree', $code_tree);
            $cmd->bindValue(':name', $name);
            $cmd->bindValue(':remark', $remark);
        } else {
            if (substr($code_tree, 0, strlen($code_tree) - 4) == substr($tree, 0, strlen($code_tree) - 4)) {
                $code_tree = $tree;
            }
            $cmd->bindValue(':name', $name);
            $cmd->bindValue(':parent_id', $parent_id);
            $cmd->bindValue(':remark', $remark);
            $cmd->bindValue(':id', $id);
            $cmd->bindValue(':code_tree', $code_tree);
        }
        $items = $cmd->execute();
        return $items > 0;
    }

    /**
     * 获取部门的code_tree编码
     * @param $parent_id
     * @return string
     */
    public static function getCodeTree($parent_id, $institution_code)
    {
        $sql = 'SELECT count(1) FROM t_department WHERE parent_id = :parent_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':parent_id', $parent_id);

        $result = $cmd->queryScalar() + 1;
        $result = sprintf('%04s', $result);

        $department = self::getDepartment($parent_id);
        if ($department != null) {
            $result = $department['code_tree'] . $result;
        } else {
            $result = $institution_code . $result;
        }
        return $result;
    }

    /**
     * 根据id获取部门
     * @param $id
     * @return array|false|null
     */
    public static function getDepartment($id)
    {
        $sql = 'SELECT * FROM t_department WHERE id = :id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $item = $cmd->queryOne();
        if ($item == false) {
            return null;
        }
        $item['code'] = $item['id'];
        return $item;
    }


    /**
     * 获取查询的数量
     * @param $name
     * @return false|null|string
     */
    public static function getCount($institution_code, $name)
    {
        $sql = 'SELECT count(1) FROM t_department WHERE institution_code = :institution_code AND name LIKE :name AND is_delete = 0';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':name', '%' . $name . '%');
        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 获取部门查询列表
     * @param $page
     * @param $pagesize
     * @param $name
     * @return array
     */
    public static function getList($page, $pagesize, $institution_code, $name)
    {
        $sql = 'SELECT * FROM t_department WHERE institution_code = :institution_code AND name LIKE :name AND is_delete = 0 ORDER BY code_tree LIMIT :start,:pagesize';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':name', '%' . $name . '%');

        //计算起始数据行
        if ($page <= 0) $page = 0;
        $start = ($page - 1) * $pagesize;

        $cmd->bindValue(':start', $start);
        $cmd->bindValue(':pagesize', $pagesize);

        $items = $cmd->queryAll();
        return $items;
    }

    /**
     * 删除部门及子部门及部门成员
     * @param $id
     * @return bool
     */
    public static function delDepartment($id)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        try {
            $code_tree = self::getDepartment($id)['code_tree'];
            $sql = 'UPDATE t_department SET is_delete = 1 WHERE code_tree like :code_tree';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':code_tree', $code_tree . '%');
            $result = $cmd->execute();
            self::delDepartmentUser($code_tree);
            $transaction->commit();
        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
            //throw $e;
        }
        return $result > 0;
    }

    /**
     * 删除指定部门及子部门的用户
     * @param $code_tree
     * @return bool
     * @throws \yii\db\Exception
     */
    public static function delDepartmentUser($code_tree)
    {
        $sql = "delete  from t_department_user   where  department_id in (select id from t_department t WHERE code_tree like :code_tree )";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':code_tree', $code_tree . '%');
        $result = $cmd->execute();

        return $result > 0;
    }

    /**
     * 获取部门树形结构(所有的)
     * @param $institution_code
     * @return array
     */
    public static function getListTree($institution_code)
    {
        $sql = 'SELECT * FROM t_department WHERE parent_id = \'\' AND institution_code = :institution_code AND is_delete = 0';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        //$items = $cmd->queryAll();
        $items=DbCacheUtility::queryAll($cmd);
        if ($items != null) {
            foreach ($items as &$item) {
                $item['code'] = $item['id'];
                $children = self::getListTreeChildren($item['id']);
                if ($children != null) {
                    $item['children'] = $children;
                }
            }
        }
        return $items;
    }


    public static function getListTree2($institution_code, $user_id)
    {
        $sql = 'SELECT * FROM t_department d JOIN  t_department_manager dm ON d.id=dm.department_id WHERE dm.manager_id = :id AND d.institution_code = :institution_code AND d.is_delete = 0';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $user_id);
        $cmd->bindValue(':institution_code', $institution_code);
        //$items = $cmd->queryAll();
        $items=DbCacheUtility::queryAll($cmd);
        if ($items != null) {
            foreach ($items as &$item) {
                $item['code'] = $item['id'];
                $children = self::getListTreeChildren($item['id']);
                if ($children != null) {
                    $item['children'] = $children;
                }
            }
        }
        return $items;
    }

    public static function getListTreeChildren($id)
    {
        $sql = 'SELECT * FROM t_department WHERE parent_id = :id AND is_delete = 0';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        //$items = $cmd->queryAll();
        $items=DbCacheUtility::queryAll($cmd);
        foreach ($items as &$item) {
            $item['code'] = $item['id'];
            $children = self::getListTreeChildren($item['id']);
            if ($children != null) {
                $item['children'] = $children;
            }
        }

        return $items;
    }

    /**
     * 添加用户到部门
     * @param $department_id
     * @param $user_id
     * @return bool
     */
    public static function addUserToDepartment($department_id, $user_id)
    {
        $sql = 'SELECT count(1) FROM t_department_user WHERE department_id = :department_id AND user_id = :user_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':department_id', $department_id);
        $cmd->bindValue(':user_id', $user_id);

        $result = $cmd->queryScalar();
        if ($result == 0) {
            $sql = 'INSERT INTO t_department_user(department_id, user_id) 
                        VALUES (:department_id,:user_id)';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':department_id', $department_id);
            $cmd->bindValue(':user_id', $user_id);
            $result = $cmd->execute();
            return $result > 0;
        }
        return false;
    }

    /**
     * 添加用户列表到部门
     * @param $department_id
     * @param $user_array
     * @return bool
     */
    public static function addUsersToDepartment($department_id, $user_array)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        $result = 0;
        try {
            for ($i = 0; $i < count($user_array); $i++) {
                $sql = 'INSERT INTO t_department_user(department_id, user_id) 
                        VALUES (:department_id,:user_id)';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':department_id', $department_id);
                $cmd->bindValue(':user_id', $user_array[$i]);
                $cmd->execute();
            }
            $result = 1;
            $transaction->commit();
        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
        }
        return $result > 0;
    }

    /**
     * 删除用户从部门
     * @param $department_id
     * @param $user_array
     * @return bool
     */
    public static function delUserFromDepartment($department_id, $user_array)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        $result = 0;
        try {
            for ($i = 0; $i < count($user_array); $i++) {
                $sql = 'DELETE FROM t_department_user WHERE department_id = :department_id AND user_id = :user_id';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':department_id', $department_id);
                $cmd->bindValue(':user_id', $user_array[$i]);
                $cmd->execute();
            }
            $result = 1;
            $transaction->commit();
        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
        }
        return $result > 0;
    }

    /**
     * 删除用户所在部门，指定了父部门，则删除其所在父子部门
     * @param $department_id
     * @param $user_id
     * @return bool
     * @throws \yii\db\Exception
     */
    public static function delUserFromDepartments($department_id, $user_id)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        $result = 0;
        try {
            $filterSQL = 'user_id = :user_id';
            $code_tree = self::getDepartment($department_id)['code_tree'];
            if ($department_id != '') {
                $filterSQL = $filterSQL . ' and department_id in (select id from t_department where code_tree like :departmentCode )';
            }
            $sql = "DELETE FROM t_department_user WHERE  $filterSQL ";
            $cmd = \Yii::$app->db->createCommand($sql);
            if ($department_id != '') {
                $cmd->bindValue(':departmentCode', $code_tree . '%');
            }
            $cmd->bindValue(':user_id', $user_id);
            $cmd->execute();

            $result = 1;
            $transaction->commit();
        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
        }
        return $result > 0;
    }

    /**
     * 根据管理员id获取与之对应的部门
     * @param $manager_id
     * @return array|false|null
     */
    public static function getDepartmentByManagerId($manager_id)
    {
        $sql = 'SELECT t2.* FROM t_department_manager t1 INNER JOIN t_department t2 ON t2.id = t1.department_id
                WHERE t1.manager_id = :manager_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':manager_id', $manager_id);
        $item = $cmd->queryOne();
        if (!$item) {
            return null;
        }
        return $item;
    }

    /**
     * 是否机构部门管理员
     * @param $manager_id
     * @return bool
     */
    public static function isDepartmentManager($manager_id)
    {
        $sql = 'SELECT count(1) FROM t_manager t0 INNER JOIN t_department_manager t1 ON t1.manager_id = t0.id
                WHERE t0.id = :manager_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue('manager_id', $manager_id);
        $result = $cmd->queryScalar();
        return $result > 0;
    }

}